﻿using ContactSMS.Commons.QueryParameters;
using ContactSMS.Commons.Util;
using ContactSMS.Domain;
using ContactSMS.Domain.Entity;
using ContactSMS.Domain.PagedResult;
using ContactSMS.Infrastructure.dbContexts;
using ContactSMS.Infrastructure.Migrations;
using Microsoft.EntityFrameworkCore;
using MySqlConnector;

namespace ContactSMS.Infrastructure
{
    public class PhoneRecordRepository : IPhoneRecordRepository
    {
        private readonly ContactDbContexts _contactDbContexts;

        public PhoneRecordRepository(ContactDbContexts contactDbContexts)
        {
            this._contactDbContexts = contactDbContexts;
        }

        public async Task<Domain.Entity.PhoneRecord?> AddPhoneRecordAsync(Domain.Entity.PhoneRecord phoneRecord)
        {
            await _contactDbContexts.PhoneRecords.AddAsync(phoneRecord);
            await _contactDbContexts.SaveChangesAsync();
            return phoneRecord;
        }

        public async Task<Domain.Entity.PhoneRecord?> DeletePhoneRecordByIdAsync(long id)
        {
            var existing = await _contactDbContexts.PhoneRecords.FirstOrDefaultAsync(x => x.Id == id);
            if (existing == null)
            {
                return null;
            }
            _contactDbContexts.PhoneRecords.Remove(existing);
            await _contactDbContexts.SaveChangesAsync();
            return existing;
        }

        public async Task<bool> DeletePhoneRecordByIdsAsync(long[] ids)
        {
            var list = await _contactDbContexts.PhoneRecords.Where(phoneRecord => ids.Contains(phoneRecord.Id)).ToListAsync();
            if (!list.Any())
            {
                return false;
            }

            var mobiles = list.Select(x => x.Mobile).ToList();
            foreach (var mobile in mobiles)
            {
                await DeletePhoneRecordByMobileAsync(mobile);
            }
            return true;
        }

        public async Task<bool> DeletePhoneRecordByMobileAsync(string mobile)
        {
            var list = await _contactDbContexts.PhoneRecords
                                    .Where(p => p.Mobile == mobile)
                                    .ToListAsync();
            if (!list.Any())
            {
                return false;
            }

            _contactDbContexts.PhoneRecords.RemoveRange(list);
            var res = await _contactDbContexts.SaveChangesAsync();
            return res > 0;
        }

        public async Task<Domain.Entity.PhoneRecord?> GetPhoneRecordByIdAsync(long id)
        {
            return await _contactDbContexts.PhoneRecords
                .Include(x => x.Contact)
                .SingleOrDefaultAsync(x => x.Id == id);
        }

        public async Task<(Domain.Entity.PhoneRecord record, List<Domain.Entity.PhoneRecord> otherRecords)> GetPhoneRecordsByIdAsync(long id)
        {
            var record = await _contactDbContexts.PhoneRecords
                .Include(x => x.Contact)
                .SingleOrDefaultAsync(x => x.Id == id);
            if (record == null)
            {
                return (null, null);
            }
            var otherRecords = await _contactDbContexts.PhoneRecords
                .Where(pr => pr.Mobile == record.Mobile)
                .ToListAsync();
            return (record, otherRecords);
        }

        public async Task<PagedPhoneRecordsResult> GetPhoneRecordsAsync(PhoneRecordQueryParameters parameters, int page = 1, int pageSize = 10)
        {
            // 获取 SQL 查询参数
            int offset = (page - 1) * pageSize;
            int limit = pageSize;

            // 使用原始 SQL 语句
            string sqlQuery = @"
    SELECT pr.*
    FROM (
        SELECT MIN(Id) as Id
        FROM t_phonerecord
        WHERE (@SearchTerm IS NULL OR Mobile LIKE CONCAT('%', @SearchTerm, '%'))
        AND (@Mobile IS NULL OR Mobile = @Mobile)
        AND (@ContactId IS NULL OR @ContactId = 0 OR ContactId = @ContactId)
        GROUP BY Mobile
        ORDER BY MIN(Id)
        LIMIT @Limit OFFSET @Offset
    ) as grouped
    JOIN t_phonerecord pr ON grouped.Id = pr.Id
    ORDER BY pr.Id;";

            // 创建参数列表
            var parameterss = new List<MySqlParameter>
{
    new MySqlParameter("@SearchTerm", string.IsNullOrEmpty(parameters.SearchTerm) ? DBNull.Value : parameters.SearchTerm),
    new MySqlParameter("@Mobile", string.IsNullOrEmpty(parameters.Mobile) ? DBNull.Value : parameters.Mobile),
    new MySqlParameter("@ContactId", string.IsNullOrEmpty(parameters.ContactId) ? DBNull.Value : parameters.ContactId),
    new MySqlParameter("@Limit", limit),
    new MySqlParameter("@Offset", offset)
};

            // 执行 SQL 查询
            var result = await _contactDbContexts.PhoneRecords
                .FromSqlRaw(sqlQuery, parameterss.ToArray())
                .ToListAsync();

            // 获取总记录数的 SQL 语句
            string countSqlQuery = @"
    SELECT COUNT(*)
    FROM (
        SELECT MIN(Id) as Id
        FROM t_phonerecord
        WHERE (@SearchTerm IS NULL OR Mobile LIKE CONCAT('%', @SearchTerm, '%'))
        AND (@Mobile IS NULL OR Mobile = @Mobile)
        AND (@ContactId IS NULL OR @ContactId = 0 OR ContactId = @ContactId)
        GROUP BY Mobile
    ) as grouped;";

            // 创建参数列表
            var countParameters = new List<MySqlParameter>
{
    new MySqlParameter("@SearchTerm", string.IsNullOrEmpty(parameters.SearchTerm) ? DBNull.Value : parameters.SearchTerm),
        new MySqlParameter("@ContactId", string.IsNullOrEmpty(parameters.ContactId) ? DBNull.Value : parameters.ContactId),
    new MySqlParameter("@Mobile", string.IsNullOrEmpty(parameters.Mobile) ? DBNull.Value : parameters.Mobile)
};

            // 获取总记录数
            var totalCount = await _contactDbContexts.Database.ExecuteSqlRawAsync(countSqlQuery, countParameters.ToArray());

            // 计算总页数
            var totalPages = (int)Math.Ceiling((double)totalCount / pageSize);

            // 提取 ContactId 列表
            var contactIds = result.Select(x => x.ContactId).ToList();
            // 通过 ContactIds 查询 Contacts
            var contactsById = await _contactDbContexts.Contacts
                .Where(c => contactIds.Contains(c.Id))
                .ToDictionaryAsync(c => c.Id, c => c);

            foreach (var item in result)
            {
                if (contactsById.TryGetValue(ConvertToNullableLong(item.ContactId), out var contact))
                {
                    item.Contact = contact;
                }
                else
                {
                    // 如果找不到对应的 Contact，则可以处理或忽略
                    item.Contact = null;
                }
            }

            // 返回结果
            return new PagedPhoneRecordsResult
            {
                PhoneRecords = result,
                CurrentPage = page,
                TotalPages = totalPages,
            };
        }

        public async Task<Domain.Entity.PhoneRecord?> UpdatePhoneRecordAsync(long id, Domain.Entity.PhoneRecord phoneRecord)
        {
            var existing = await _contactDbContexts.PhoneRecords.FirstOrDefaultAsync(x => x.Id == id);
            if (existing == null)
            {
                return null;
            }
            existing.Mobile = phoneRecord.Mobile;
            existing.UpdateTime = TimeUtil.GetTimestamp();
            await _contactDbContexts.SaveChangesAsync();
            return existing;
        }

        private IQueryable<Domain.Entity.PhoneRecord> ApplySorting(IQueryable<Domain.Entity.PhoneRecord> query, string sortBy, bool sortDescending)
        {
            // 根据排序字段和排序方向对查询进行排序
            switch (sortBy.ToLower())
            {
                case "mobile":
                    query = sortDescending ? query.OrderByDescending(c => c.Mobile) : query.OrderBy(c => c.Mobile);
                    break;
                // 可以根据需要添加更多的排序字段
                default:
                    // 默认按照 ID 排序
                    query = sortDescending ? query.OrderByDescending(c => c.Id) : query.OrderBy(c => c.Id);
                    break;
            }

            return query;
        }


        private static long ConvertToNullableLong(long value)
        {
            return value;
        }

        public async Task<List<Domain.Entity.PhoneRecord>> SynchAsync(List<Domain.Entity.PhoneRecord> phoneRecords)
        {
            await _contactDbContexts.PhoneRecords.AddRangeAsync(phoneRecords);
            await _contactDbContexts.SaveChangesAsync();
            return phoneRecords;
        }
    }
}
